
namespace Pixels.ListItems.DataLayer
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
    using Pixels.DataLayer;
    using Pixels.Exceptions;

    internal class DataAccessLayer : IDataAccessLayer
    {
        private DbHelper helper;

        #region Constructors
        public DataAccessLayer()
        {
            helper = new CommonDbHelper();
        }
        public DataAccessLayer(string ConnectionStringName)
        {
            helper = new CommonDbHelper(ConnectionStringName);
        }
        #endregion

        public IListItem ListItemAddEdit(IListItem objListItem, bool updateifExists)
        {
            try
            {
                ListItem _listitem = (ListItem)ListItemFetch(objListItem.ListItemID);

                if (updateifExists == false && _listitem != null)
                    throw new Exception("ListItem already exists.");

                if (_listitem == null)
                    helper.ExecuteNonQuery(SqlQueryBuilder.GetInsertQuery(objListItem), CommandType.Text);
                else
                {
                    objListItem.ListItemID = _listitem.ListItemID;

                    helper.ExecuteNonQuery(SqlQueryBuilder.GetUpdateQuery(objListItem, "ListItemID"), CommandType.Text);     
                }

                return objListItem;
            }
            catch (DbException DbEx)
            {
                bool rethrow = ExceptionPolicy.HandleException(DbEx, "Data Layer Policy");
                throw new DLWrappedException("ListItem could not be added/modified. Please Contact System Administrator");
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public void ListItemDelete(Guid ListItemID)
        {
            try
            {
                helper.ExecuteNonQuery(DBConstants.ListItemDelete, ListItemID);
            }
            catch (DbException DbEx)
            {
                bool rethrow = ExceptionPolicy.HandleException(DbEx, "Data Layer Policy");
                throw new DLWrappedException("ListItem not be deleted");
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public IListItem ListItemFetch(Guid ListItemID)
        {
            ListItem objListItem = null;
            try
            {
                using (IDataReader reader = helper.ExecuteReader(DBConstants.ListItemFetchByID, ListItemID))
                {
                    if ((reader != null) && (reader.Read()))
                    {
                        objListItem = new ListItem();

                        LoadToListItem(objListItem, reader);
                    }
                    reader.Close();
                    return objListItem;
                }
            }
            catch (DbException DbEx)
            {
                bool rethrow = ExceptionPolicy.HandleException(DbEx, "Data Layer Policy");
                throw new DLWrappedException("ListItem Detail could not be found");
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public IList<IListItem> ListItemFetchByQuery(string Query)
        {
            IList<IListItem> ListItems = null;
            try
            {
                using (IDataReader reader = helper.ExecuteReader(DBConstants.ListItemFetchByQuery, Query))
                {
                    if ((reader != null))
                    {
                        ListItems = new List<IListItem>();
                        while (reader.Read())
                        {
                            ListItem objListItem = new ListItem();

                            LoadToListItem(objListItem, reader);

                            ListItems.Add(objListItem);
                        }
                    }
                    reader.Close();
                    return ListItems;
                }
            }
            catch (DbException DbEx)
            {
                bool rethrow = ExceptionPolicy.HandleException(DbEx, "Data Layer Policy");
                throw new DLWrappedException("Error in Fetching ListItem Details.");
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public IListItem ListItemFetchByExtentedListItemID(Guid ExtentedListItemID)
        {
            ListItem objListItem = null;
            try
            {
                using (IDataReader reader = helper.ExecuteReader(DBConstants.ListItemFetchByID, ExtentedListItemID))
                {
                    if ((reader != null) && (reader.Read()))
                    {
                        objListItem = new ListItem();

                        LoadToListItem(objListItem, reader);
                    }
                    reader.Close();
                    return objListItem;
                }
            }
            catch (DbException DbEx)
            {
                bool rethrow = ExceptionPolicy.HandleException(DbEx, "Data Layer Policy");
                throw new DLWrappedException("ListItem Detail could not be found");
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }        

        public IList<IListItem> ListItemsFetch()
        {
            IList<IListItem> ListItems = null;
            try
            {
                using (IDataReader reader = helper.ExecuteReader(DBConstants.ListItemsFetch, CommandType.Text))
                {
                    if ((reader != null))
                    {
                        ListItems = new List<IListItem>();
                        while (reader.Read())
                        {
                            ListItem objListItem = new ListItem();

                            LoadToListItem(objListItem, reader);

                            ListItems.Add(objListItem);
                        }
                    }
                    reader.Close();
                    return ListItems;
                }
            }
            catch (DbException DbEx)
            {
                bool rethrow = ExceptionPolicy.HandleException(DbEx, "Data Layer Policy");
                throw new DLWrappedException("Error in Fetching ListItem Details.");
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        private void LoadToListItem(ListItem objListItem, IDataReader reader)
        {
            objListItem.ListItemID = Guid.Parse(reader["ListItemID"].ToString());
            objListItem.Title = reader["Title"].ToString();
            objListItem.Description = reader["Description"].ToString();
            DateTime createdDate, modifiedDate;
            DateTime.TryParse(reader["CreatedDate"].ToString(), out createdDate);
            DateTime.TryParse(reader["ModifiedDate"].ToString(), out modifiedDate);
            objListItem.CreatedDate = createdDate;
            objListItem.ModifiedDate = modifiedDate;                                   
            Guid baseListItemID;
            Guid.TryParse(reader["BaseListItemID"] == null ? "" : reader["BaseListItemID"].ToString(), out baseListItemID);
            objListItem.BaseListItemID = baseListItemID;
        }
    }
}
